#include "database.h"
#include "login.h"


DataBase::DataBase()
{

}


//建立一个数据库连接
bool DataBase::createConnection()
{
    //以后就可以用"sqlite1"与数据库进行连接了
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "sqlite1");
    db.setDatabaseName(".//userMessage.db");
    if( !db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    return true;
}

//创建数据库表
bool DataBase::createTable()
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    QSqlQuery query(db);
    //查询SQL数据库，并返回是否成功
//    bool success = query.exec("create table automobil(id int primary key,attribute varchar,"
//                              "type varchar,kind varchar,nation int,carnumber int,elevaltor int,"
//                              "distance int,oil int,temperature int)");
    bool success = query.exec("create table userinfo(username varchar primary key,password varchar,nickname varchar,sex varchar,userdate date,phone varchar)");
    if(success)
    {
        qDebug() << QObject::tr("数据库表创建成功！\n");
        return true;
    }
    else
    {
        qDebug() << QObject::tr("数据库表创建失败！\n");
        return false;
    }
}


////查询所有信息
//bool DataBase::queryAll()
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
//    QSqlQuery query(db);
//    query.exec("select * from automobil");
//    QSqlRecord rec = query.record();
//    qDebug() << QObject::tr("automobil表字段数：" ) << rec.count();

//    while(query.next())
//    {
//        for(int index = 0; index < 10; index++)
//            qDebug() << query.value(index) << " ";
//        qDebug() << "\n";
//    }
//}



////根据ID删除记录
//bool DataBase::deleteById(int id)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
//    QSqlQuery query(db);
//    query.prepare(QString("delete from automobil where id = %1").arg(id));
//    if(!query.exec())
//    {
//        qDebug() << "删除记录失败！";
//        return false;
//    }
//    return true;
//}

////根据ID更新记录
//bool DataBase::updateById(int id)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
//    QSqlQuery query(db);
//    query.prepare(QString("update automobil set attribute=?,type=?,"
//                             "kind=?, nation=?,"
//                             "carnumber=?, elevaltor=?,"
//                             "distance=?, oil=?,"
//                             "temperature=? where id=%1").arg(id));

//     query.bindValue(0,"四轮");
//     query.bindValue(1,"轿车");
//     query.bindValue(2,"富康");
//     query.bindValue(3,rand()%100);
//     query.bindValue(4,rand()%10000);
//     query.bindValue(5,rand()%300);
//     query.bindValue(6,rand()%200000);
//     query.bindValue(7,rand()%52);
//     query.bindValue(8,rand()%100);

//     bool success=query.exec();
//     if(!success)
//     {
//          QSqlError lastError = query.lastError();
//          qDebug() << lastError.driverText() << QString(QObject::tr("更新失败"));
//     }
//    return true;
//}

////排序
//bool DataBase::sortById()
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
//    QSqlQuery query(db);
//    bool success=query.exec("select * from automobil order by id desc");
//    if(success)
//    {
//        qDebug() << QObject::tr("排序成功");
//        return true;
//    }
//    else
//    {
//        qDebug() << QObject::tr("排序失败！");
//        return false;
//    }
//}



//用户名匹配
bool DataBase::queryUsername(const QString &username)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    if( !db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    QSqlQuery query("select username from userinfo",db);
    QSqlRecord rec = query.record();
    while(query.next())
    {
        rec = query.record();
        int snamecol = rec.indexOf("username");
        QString value = query.value(snamecol).toString();
        qDebug()<<"username"<<value;
        if(value==username)
        {
            qDebug()<<"找到了";
            return true;
        }
    }
    return false;
}

//账号密码匹配
bool DataBase::querypassword(const QString &username,const QString &password)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    if( !db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    QString S = QString("select username,password from userinfo where username='%1' and password='%2'").arg(username).arg(password);
    QSqlQuery query(S,db);
    if(!query.exec())//执行S的SQL语句
    {
        qDebug() << "Error: Fail to query table. " << query.lastError();
    }
    QSqlRecord rec = query.record();
//    qDebug()<<"rec:"<<rec;
    while(query.next())
    {
        rec = query.record();
        int UsernameCol = rec.indexOf("username");//获取username列号
        int PasswordCol = rec.indexOf("password");//获取username列号
        QString value_username = query.value(UsernameCol).toString();//获取值
        QString value_password = query.value(PasswordCol).toString();
//        qDebug()<<"username"<<value_username<<"password"<<value_password;
        if(value_username==username&&value_password==password)
        {
            qDebug()<<"密码正确";
            return true;
        }
    }
    return false;

}

//注册
bool DataBase::UserSignUp(QString username,QString password)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    if( !db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    if(!DataBase::queryUsername(username))
    {
        QSqlQuery query(db);
        query.prepare("insert into userinfo values(:username, :password)");
        query.bindValue(":username", username);//插入数据
        query.bindValue(":password", password);
        bool success=query.exec();
        if(!success)
        {
            QSqlError lastError = query.lastError();
            qDebug() << lastError.driverText() << QString(QObject::tr("插入失败"));
            return false;
        }
        qDebug()<<"添加成功";
        return true;
    }
    qDebug()<<"添加失败";
    QMessageBox::information(NULL,"登录","用户名已存在",QMessageBox::Ok);
    db.close();
    return false;

}

//更新密码
bool DataBase::updatePassword(const QString &username,const QString &password)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
    if( !db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    QSqlQuery query(db);
    query.prepare("update userinfo set password=? where username=? ");
    query.addBindValue(password);//填入新密码
    query.addBindValue(username);


     bool success=query.exec();//执行
     if(!success)
     {
          QSqlError lastError = query.lastError();
          qDebug() << lastError.driverText() << QString(QObject::tr("更新失败"));
     }
    return true;
}
